1 Imports System.Data.SqlClient
2 Imports System.IO
3
4 Public Class frmStock
5
6 Private Sub auto()
7 Try
8 Dim Num As Integer = 0
9 con = New SqlConnection(cs)
10 con.Open()
11 Dim Sql As String = ("SELECT MAX(ST_ID) FROM Stock")
12 cmd = New SqlCommand(Sql)
13 cmd.Connection = con
14 If (IsDBNull(cmd.ExecuteScalar)) Then
15 Num = 1
16 txtST_ID.Text = Num.ToString
17 txtStockID.Text = "ST" + Num.ToString
18 Else
19 Num = cmd.ExecuteScalar + 1
20 txtST_ID.Text = Num.ToString
21 txtStockID.Text = "ST" + Num.ToString
22 End If
23 con.Close()
24 Catch ex As Exception
25 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
26 End Try
27 End Sub
28 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
29 Me.Close()
30 End Sub
31
32 Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
33 If Len(Trim(txtSupplierID.Text)) = 0 Then
34 MessageBox.Show("Please retrieve supplier id", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
35 txtSupplierID.Focus()
36 Exit Sub
37 End If
38 If DataGridView1.Rows.Count = 0 Then
39 MessageBox.Show("Sorry no product info added to grid", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
40 Exit Sub
41 End If
42 If Len(Trim(txtTotalPayment.Text)) = 0 Then
43 MessageBox.Show("Please enter total payment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
44 txtTotalPayment.Focus()
45 Exit Sub
46 End If
47 If Val(txtTotalPayment.Text) > Val(txtGrandTotal.Text) Then
48 MessageBox.Show("Total payment can not be more than grand total", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
49 txtTotalPayment.Focus()
50 Exit Sub
51 End If
52 Try
53 For Each row As DataGridViewRow In DataGridView1.Rows
54 If Not row.IsNewRow Then
55 con = New SqlConnection(cs)
56 con.Open()
57 Dim ct As String = "select ProductID from Temp_Stock where ProductID=@d1"
58 cmd = New SqlCommand(ct)
59 cmd.Connection = con
60 cmd.Parameters.AddWithValue("@d1", row.Cells(0).Value.ToString())
61 rdr = cmd.ExecuteReader()
62 If (rdr.Read()) Then
63
64 con = New SqlConnection(cs)
65 con.Open()
66 Dim cb2 As String = "Update Temp_Stock set Qty = Qty + " & row.Cells(3).Value & " where ProductID=@d1"
67 cmd = New SqlCommand(cb2)
68 cmd.Connection = con
69 cmd.Parameters.AddWithValue("@d1", row.Cells(0).Value.ToString())
70 cmd.ExecuteReader()
71 con.Close()
72
73 Else
74 con = New SqlConnection(cs)
75 con.Open()
76 Dim cb3 As String = "insert into Temp_Stock(ProductID,Qty) VALUES (@d1,@d2)"
77 cmd = New SqlCommand(cb3)
78 cmd.Connection = con
79 cmd.Parameters.AddWithValue("@d1", row.Cells(0).Value.ToString())
80 cmd.Parameters.AddWithValue("@d2", row.Cells(3).Value)
81 cmd.ExecuteReader()
82 con.Close()
83 End If
84 End If
85 Next
86 con = New SqlConnection(cs)
87 con.Open()
88 Dim cb As String = "insert into Stock(ST_ID, Stock_ID, [Date], SupplierID, GrandTotal, TotalPayment, PaymentDue, Remarks) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8)"
89 cmd = New SqlCommand(cb)
90 cmd.Parameters.AddWithValue("@d1", txtST_ID.Text)
91 cmd.Parameters.AddWithValue("@d2", txtStockID.Text)
92 cmd.Parameters.AddWithValue("@d3", dtpDate.Value.Date)
93 cmd.Parameters.AddWithValue("@d4", txtSup_ID.Text)
94 cmd.Parameters.AddWithValue("@d5", txtGrandTotal.Text)
95 cmd.Parameters.AddWithValue("@d6", txtTotalPayment.Text)
96 cmd.Parameters.AddWithValue("@d7", txtPaymentDue.Text)
97 cmd.Parameters.AddWithValue("@d8", txtRemarks.Text)
98 cmd.Connection = con
99 cmd.ExecuteNonQuery()
100 con.Close()
101 con = New SqlConnection(cs)
102 con.Open()
103 Dim cb1 As String = "insert into Stock_Product(StockID,ProductID,Qty,Price,TotalAmount) VALUES (" & txtST_ID.Text & ",@d1,@d2,@d3,@d4)"
104 cmd = New SqlCommand(cb1)
105 cmd.Connection = con
106 ' Prepare command for repeated execution
107 cmd.Prepare()
108 ' Data to be inserted
109 For Each row As DataGridViewRow In DataGridView1.Rows
110 If Not row.IsNewRow Then
111 cmd.Parameters.AddWithValue("@d1", row.Cells(0).Value)
112 cmd.Parameters.AddWithValue("@d2", row.Cells(3).Value)
113 cmd.Parameters.AddWithValue("@d3", row.Cells(4).Value)
114 cmd.Parameters.AddWithValue("@d4", row.Cells(5).Value)
115 cmd.ExecuteNonQuery()
116 cmd.Parameters.Clear()
117 End If
118 Next
119 con.Close()
120 RefreshRecords()
121 LogFunc(lblUser.Text, "added the new stock having stock id '" & txtStockID.Text & "'")
122 MessageBox.Show("Successfully saved", "Stock", MessageBoxButtons.OK, MessageBoxIcon.Information)
123 btnSave.Enabled = False
124 con.Close()
125 Catch ex As Exception
126 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
127 End Try
128 End Sub
129
130 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
131 frmSupplierRecord.lblSet.Text = "Stock Entry"
132 frmSupplierRecord.Reset()
133 frmSupplierRecord.ShowDialog()
134 End Sub
135
136 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
137 frmProductRecord.lblSet.Text = "Stock"
138 frmProductRecord.Reset()
139 frmProductRecord.ShowDialog()
140 End Sub
141 Sub Reset()
142 txtGrandTotal.Text = ""
143 txtPricePerQty.Text = ""
144 txtProductCode.Text = ""
145 txtProductName.Text = ""
146 txtQty.Text = ""
147 txtRemarks.Text = ""
148 txtSupplierID.Text = ""
149 txtSupplierName.Text = ""
150 txtTotalAmount.Text = ""
151 txtTotalPayment.Text = ""
152 dtpDate.Text = Today
153 DataGridView1.Rows.Clear()
154 btnSave.Enabled = True
155 btnUpdate.Enabled = False
156 btnRemove.Enabled = False
157 txtPaymentDue.Text = ""
158 dtpDate.Enabled = True
159 DataGridView1.Enabled = True
160 btnAdd.Enabled = True
161 auto()
162 End Sub
163 Private Sub btnNew_Click(sender As System.Object, e As System.EventArgs) Handles btnNew.Click
164 Reset()
165 End Sub
166
167 Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click
168 Try
169 If txtProductCode.Text = "" Then
170 MessageBox.Show("Please retrieve product code", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
171 txtProductCode.Focus()
172 Exit Sub
173 End If
174 If txtQty.Text = "" Then
175 MessageBox.Show("Please enter quantity", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
176 txtQty.Focus()
177 Exit Sub
178 End If
179 If txtQty.Text = 0 Then
180 MessageBox.Show("Quantity can not be zero", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
181 txtQty.Focus()
182 Exit Sub
183 End If
184 If txtPricePerQty.Text = "" Then
185 MessageBox.Show("Please enter price per qty.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
186 txtPricePerQty.Focus()
187 Exit Sub
188 End If
189 If DataGridView1.Rows.Count = 0 Then
190 DataGridView1.Rows.Add(txtProductID.Text, txtProductCode.Text, txtProductName.Text, txtQty.Text, txtPricePerQty.Text, txtTotalAmount.Text)
191 Dim k As Double = 0
192 k = GrandTotal()
193 k = Math.Round(k, 2)
194 txtGrandTotal.Text = k
195 Clear()
196 Exit Sub
197 End If
198 For Each r As DataGridViewRow In Me.DataGridView1.Rows
199 If r.Cells(1).Value = txtProductCode.Text Then
200 r.Cells(0).Value = txtProductID.Text
201 r.Cells(1).Value = txtProductCode.Text
202 r.Cells(2).Value = txtProductName.Text
203 r.Cells(3).Value = Val(r.Cells(3).Value) + Val(txtQty.Text)
204 r.Cells(4).Value = txtPricePerQty.Text
205 r.Cells(5).Value = Val(r.Cells(5).Value) + Val(txtTotalAmount.Text)
206 Dim i As Double = 0
207 i = GrandTotal()
208 i = Math.Round(i, 2)
209 txtGrandTotal.Text = i
210 Clear()
211 Exit Sub
212 End If
213 Next
214 DataGridView1.Rows.Add(txtProductID.Text, txtProductCode.Text, txtProductName.Text, txtQty.Text, txtPricePerQty.Text, txtTotalAmount.Text)
215 Dim j As Double = 0
216 j = GrandTotal()
217 j = Math.Round(j, 2)
218 txtGrandTotal.Text = j
219 Clear()
220 Catch ex As Exception
221 MsgBox(ex.Message)
222 End Try
223 End Sub
224 Public Function GrandTotal() As Double
225 Dim sum As Double = 0
226 Try
227 For Each r As DataGridViewRow In Me.DataGridView1.Rows
228 sum = sum + r.Cells(5).Value
229 Next
230 Catch ex As Exception
231 MsgBox(ex.Message)
232 End Try
233 Return sum
234 End Function
235 Sub Clear()
236 txtProductCode.Text = ""
237 txtProductName.Text = ""
238 txtQty.Text = ""
239 txtPricePerQty.Text = ""
240 txtTotalAmount.Text = ""
241 End Sub
242
243 Private Sub btnRemove_Click(sender As System.Object, e As System.EventArgs) Handles btnRemove.Click
244 Try
245 For Each row As DataGridViewRow In DataGridView1.SelectedRows
246 DataGridView1.Rows.Remove(row)
247 Next
248 Dim k As Double = 0
249 k = GrandTotal()
250 k = Math.Round(k, 2)
251 txtGrandTotal.Text = k
252 Compute()
253 btnRemove.Enabled = False
254 Catch ex As Exception
255 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
256 End Try
257 End Sub
258 Sub Compute()
259 Dim i As Double = 0
260 i = Val(txtGrandTotal.Text) - Val(txtTotalPayment.Text)
261 i = Math.Round(i, 2)
262 txtPaymentDue.Text = i
263 End Sub
264
265 Private Sub DataGridView1_MouseClick(sender As System.Object, e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseClick
266 If DataGridView1.Rows.Count > 0 Then
267 btnRemove.Enabled = True
268 End If
269 End Sub
270
271 Private Sub DataGridView1_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridView1.RowPostPaint
272 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
273 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
274 If DataGridView1.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
275 DataGridView1.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
276 End If
277 Dim b As Brush = SystemBrushes.ControlText
278 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
279
280 End Sub
281
282 Private Sub frmStock_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
283
284 End Sub
285
286 Private Sub txtPricePerQty_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtPricePerQty.TextChanged
287 Dim i As Double = 0
288 i = CDbl(Val(txtQty.Text) * Val(txtPricePerQty.Text))
289 i = Math.Round(i, 2)
290 txtTotalAmount.Text = i
291 End Sub
292
293
294 Private Sub txtQty_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtQty.TextChanged
295 Dim i As Double = 0
296 i = CDbl(Val(txtQty.Text) * Val(txtPricePerQty.Text))
297 i = Math.Round(i, 2)
298 txtTotalAmount.Text = i
299 End Sub
300
301 Private Sub txtQty_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtQty.KeyPress
302 If (e.KeyChar < Chr(48) Or e.KeyChar > Chr(57)) And e.KeyChar <> Chr(8) Then
303 e.Handled = True
304 End If
305 End Sub
306
307 Private Sub txtPricePerQty_KeyPress(sender As System.Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtPricePerQty.KeyPress
308 Dim keyChar = e.KeyChar
309
310 If Char.IsControl(keyChar) Then
311 'Allow all control characters.
312 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
313 Dim text = Me.txtPricePerQty.Text
314 Dim selectionStart = Me.txtPricePerQty.SelectionStart
315 Dim selectionLength = Me.txtPricePerQty.SelectionLength
316
317 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
318
319 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
320 'Reject an integer that is longer than 16 digits.
321 e.Handled = True
322 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
323 'Reject a real number with two many decimal places.
324 e.Handled = False
325 End If
326 Else
327 'Reject all other characters.
328 e.Handled = True
329 End If
330 End Sub
331
332 Private Sub txtTotalPayment_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtTotalPayment.TextChanged
333 Compute()
334 End Sub
335
336 Private Sub txtTotalPayment_Validating(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles txtTotalPayment.Validating
337 If Val(txtTotalPayment.Text) > Val(txtGrandTotal.Text) Then
338 MessageBox.Show("Total payment can not be more than grand total", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
339 End If
340 Exit Sub
341 End Sub
342
343 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
344 frmStockRecord.Reset()
345 frmStockRecord.ShowDialog()
346 End Sub
347
348 Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
349 If Len(Trim(txtSupplierID.Text)) = 0 Then
350 MessageBox.Show("Please retrieve supplier id", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
351 txtSupplierID.Focus()
352 Exit Sub
353 End If
354 If DataGridView1.Rows.Count = 0 Then
355 MessageBox.Show("Sorry no product info added to grid", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
356 Exit Sub
357 End If
358 If Len(Trim(txtTotalPayment.Text)) = 0 Then
359 MessageBox.Show("Please enter total payment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
360 txtTotalPayment.Focus()
361 Exit Sub
362 End If
363 If Val(txtTotalPayment.Text) > Val(txtGrandTotal.Text) Then
364 MessageBox.Show("Total payment can not be more than grand total", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
365 txtTotalPayment.Focus()
366 Exit Sub
367 End If
368 Try
369 con = New SqlConnection(cs)
370 con.Open()
371 Dim cb As String = "Update Stock set Stock_ID=@d2, [Date]=@d3, SupplierID=@d4, GrandTotal=@d5, TotalPayment=@d6, PaymentDue=@d7, Remarks=@d8 where ST_ID=@d1"
372 cmd = New SqlCommand(cb)
373 cmd.Parameters.AddWithValue("@d2", txtStockID.Text)
374 cmd.Parameters.AddWithValue("@d3", dtpDate.Value)
375 cmd.Parameters.AddWithValue("@d4", txtSup_ID.Text)
376 cmd.Parameters.AddWithValue("@d5", txtGrandTotal.Text)
377 cmd.Parameters.AddWithValue("@d6", txtTotalPayment.Text)
378 cmd.Parameters.AddWithValue("@d7", txtPaymentDue.Text)
379 cmd.Parameters.AddWithValue("@d8", txtRemarks.Text)
380 cmd.Parameters.AddWithValue("@d1", txtST_ID.Text)
381 cmd.Connection = con
382 cmd.ExecuteNonQuery()
383 con.Close()
384 RefreshRecords()
385 LogFunc(lblUser.Text, "updated stock having stock id '" & txtStockID.Text & "'")
386 MessageBox.Show("Successfully updated", "Stock", MessageBoxButtons.OK, MessageBoxIcon.Information)
387 btnUpdate.Enabled = False
388 con.Close()
389 Catch ex As Exception
390 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
391 End Try
392 End Sub
393 End Class
